package com.jeedcp.gen.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jeedcp.gen.dao.DibotColumDao;
import com.jeedcp.gen.dao.TableMapper;
import com.jeedcp.gen.entity.GenColumn;
import com.jeedcp.gen.model.GenTableColumn;
import com.jeedcp.gen.service.CommonService;
import com.jeedcp.gen.utils.DBUtil;
import com.jeedcp.gen.utils.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service("commonService")
public class CommonServiceImpl implements CommonService {

    @Autowired
    TableMapper tableMapper;
    @Autowired
    DibotColumDao dibotColumDao;

    @Override
    public R updatetable(List<GenTableColumn> genTableColumnList) throws SQLException, ClassNotFoundException {
        for (GenTableColumn genTableColumn:genTableColumnList){
            if (genTableColumn.getColName().equals("")){
                return R.ok("请输入字段名称");
            }else if (genTableColumn.getFieldLabel().equals("")){
                return R.ok("请输入字段标签");
            }
            if (genTableColumn.getExtensionList().equals("yyyy-MM-dd")&&genTableColumn.getDefaultValue().equals("CURRENT_TIMESTAMP")){
                return R.ok("date类型无法使用默认值");
            }
        }
        String tableName = genTableColumnList.get(0).getTableName();
        List<Map> tableColumn = tableMapper.listTableColumn(tableName);
        List<GenTableColumn> result = new ArrayList<>();
        List<GenTableColumn> addgenTableColumnList = new ArrayList<>();
        List<GenTableColumn> deletegenTableColumnList = new ArrayList<>();

        for (Map map : tableColumn) {
//            GenColumn genColumn=dibotColumDao.selectOne(new QueryWrapper<GenColumn>().eq("table_name",(String) map.get("TABLE_NAME")).eq("col_name",(String) map.get("COLUMN_NAME")));
            GenTableColumn genTableColumn = new GenTableColumn();
            genTableColumn.setActive(true);
            genTableColumn.setColIndex(Integer.valueOf(String.valueOf(map.get("ORDINAL_POSITION"))) - 1);
            genTableColumn.setColType((String) map.get("COLUMN_TYPE"));
            genTableColumn.setTableName((String) map.get("TABLE_NAME"));

            genTableColumn.setColName((String) map.get("COLUMN_NAME"));
            genTableColumn.setDecimal(0);
            System.out.println(genTableColumn.getColName());
            String colLength=(String) map.get("COLUMN_TYPE");
            if(colLength.contains("(")){
                if (colLength.contains(",")) {
                    colLength = colLength.substring(colLength.indexOf("(") + 1, colLength.indexOf(","));
                } else if (colLength.contains("timestamp") || colLength.contains("date")) {
                    colLength = "0";
                } else {
                    colLength = colLength.substring(colLength.indexOf("(") + 1, colLength.indexOf(")"));
                }
                genTableColumn.setColLength(Integer.valueOf(colLength));
            }else{
                genTableColumn.setColLength(0);
            }

            genTableColumn.setFieldLabel((String) map.get("COLUMN_COMMENT"));
            genTableColumn.setFormType("INPUT");
            genTableColumn.setFieldName((String) map.get("COLUMN_NAME"));
            genTableColumn.setFieldPackagingType((String) map.get("DATA_TYPE"));
            genTableColumn.setRefType("");
            genTableColumn.setHasReference(false);
            genTableColumn.setHasReferenceTable(false);
            if (map.get("COLUMN_DEFAULT") == null) {
                genTableColumn.setDefaultValue("");
            } else {
                genTableColumn.setDefaultValue((String) map.get("COLUMN_DEFAULT"));
            }
            if ("NO".equals(map.get("IS_NULLABLE"))) {
                genTableColumn.setNotnull(1);
            } else {
                genTableColumn.setNotnull(0);
            }
            if (genTableColumn.getFieldPackagingType().contains("Date")) {
                if (genTableColumn.getColType().contains("timestamp")){
                    genTableColumn.setExtensionList("yyyy-MM-dd HH:mm");
                }else {
                    genTableColumn.setExtensionList("yyyy-MM-dd");
                }
            } else if (genTableColumn.getFieldPackagingType().contains("decimal")) {
                genTableColumn.setExtensionList("fixed");
            } else if (genTableColumn.getFieldPackagingType().contains("double")) {
                genTableColumn.setExtensionList("float");
            } else if (genTableColumn.getColType().contains("unsigned")) {
                genTableColumn.setExtensionList("unsigned");
            } else {
                genTableColumn.setExtensionList("");
            }
            result.add(genTableColumn);
        }

        //删除字段
        for (int i = 0; i < result.size(); i++) {
            boolean a = false;
            for (GenTableColumn genTableColumn1 : genTableColumnList) {
                if (genTableColumn1.getColName().contains(result.get(i).getColName())) {
                    a = true;
                }
            }
            if (a == false) {
                deletegenTableColumnList.add(result.get(i));
            }
        }
        for (GenTableColumn genTableColumn : deletegenTableColumnList) {
            String sql = "alter table " + genTableColumn.getTableName() + " drop column `" + genTableColumn.getColName() + "`; ";
            DBUtil.executeUpdateFH(sql);
        }

        //新增字段
        for (int i = 0; i < genTableColumnList.size(); i++) {
            boolean a = false;
            for (GenTableColumn genTableColumn1 : result) {
                if (genTableColumn1.getColName().equals(genTableColumnList.get(i).getColName())) {
                    a = true;
                }
            }
            if (a == false) {
                genTableColumnList.get(i).setFieldName(genTableColumnList.get(i - 1).getColName());
                addgenTableColumnList.add(genTableColumnList.get(i));
            }
        }
        for (GenTableColumn genTableColumn : addgenTableColumnList) {
            if (genTableColumn.getExtensionList().contains("yyyy")) {
                if (genTableColumn.getExtensionList().contains("HH:mm")) {
                    if (genTableColumn.getDefaultValue().contains("CURRENT_TIMESTAMP")) {
                        String sql9 = "alter table " + genTableColumn.getTableName() + "  ADD COLUMN " + genTableColumn.getColName() + " timestamp  ";
                        if (String.valueOf(genTableColumn.getNotnull()).contains("1")) {
                            sql9 = sql9 + " NOT NULL ";
                        }
                        sql9 = sql9 + " DEFAULT  CURRENT_TIMESTAMP  COMMENT '" + genTableColumn.getFieldLabel() + "' AFTER `" + genTableColumn.getFieldName() + "`";
                        DBUtil.executeUpdateFH(sql9);
                    } else {
                        String sql9 = "alter table " + genTableColumn.getTableName() + "  ADD COLUMN " + genTableColumn.getColName() + " timestamp   ";
                        if (String.valueOf(genTableColumn.getNotnull()).contains("1")) {
                            sql9 = sql9 + " NOT NULL ";
                        }
                        sql9 = sql9 + " COMMENT '" + genTableColumn.getFieldLabel() + "' AFTER `" + genTableColumn.getFieldName() + "`";
                        DBUtil.executeUpdateFH(sql9);
                    }
                } else {
                    String sql9 = "alter table " + genTableColumn.getTableName() + "  ADD COLUMN " + genTableColumn.getColName() + " date  NOT NULL    COMMENT '" + genTableColumn.getFieldLabel() + "'";
                    DBUtil.executeUpdateFH(sql9);
                }
            } else {
                String leixing = "";
                if (genTableColumn.getFieldPackagingType().equals("String")) {
                    leixing = "varchar";
                } else if (genTableColumn.getFieldPackagingType().equals("Long")) {
                    leixing = "bigint";
                } else if (genTableColumn.getFieldPackagingType().equals("Integer")) {
                    leixing = "smallint";
                } else if (genTableColumn.getFieldPackagingType().equals("Boolean")) {
                    leixing = "tinyint";
                } else if (genTableColumn.getFieldPackagingType().equals("Double")) {
                    if (genTableColumn.getExtensionList().contains("fixed")) {
                        leixing = "decimal";
                    } else if (genTableColumn.getExtensionList().contains("float")) {
                        leixing = "double";
                    }
                }
                String sql9="";
                if (leixing.contains("decimal")||leixing.contains("double")){
                    sql9 = "alter table " + genTableColumn.getTableName() + "  ADD COLUMN `" + genTableColumn.getColName() + "` " + leixing + "(" + genTableColumn.getColLength() + "," + genTableColumn.getDecimal() + ")";
                }
                else {
                    sql9 = "alter table " + genTableColumn.getTableName() + "  ADD COLUMN `" + genTableColumn.getColName() + "` " + leixing + "(" + genTableColumn.getColLength() +  ")";
                }
                if (genTableColumn.getExtensionList().contains("unsigned")) {
                    sql9 = sql9 + " unsigned ";
                }
                if (String.valueOf(genTableColumn.getNotnull()).contains("1")) {
                    sql9 = sql9 + " NOT NULL ";
                }
                if (genTableColumn.getDefaultValue() != null) {
                    if (genTableColumn.getDefaultValue() != "") {
                        sql9 = sql9 + " DEFAULT '" + genTableColumn.getDefaultValue()+"'";
                    }
                }
                sql9 = sql9 + " COMMENT '" + genTableColumn.getFieldLabel() + "' AFTER `" + genTableColumn.getFieldName() + "`";
                DBUtil.executeUpdateFH(sql9);
            }
        }

        //修改字段
        for (int i = 0; i < genTableColumnList.size(); i++) {
            if (String.valueOf(genTableColumnList.get(i).getIsQuery()).contains("0")&&!genTableColumnList.get(i).getQueryType().equals("")){
                return R.ok("请勾选查询按钮");
            }
            if (String.valueOf(genTableColumnList.get(i).getIsQuery()).contains("1")&&genTableColumnList.get(i).getQueryType().equals("")){
                return R.ok("请选择查询方式");
            }
            for (GenTableColumn genTableColumn1 : result) {
                if (genTableColumn1.getColName().contains(genTableColumnList.get(i).getColName())) {
                    if (genTableColumn1.getFieldLabel() != null && genTableColumnList.get(i).getFieldLabel() != null && genTableColumn1.getDefaultValue() != null && genTableColumnList.get(i).getDefaultValue() != null) {
                        if (!genTableColumn1.getFieldLabel().equals(genTableColumnList.get(i).getFieldLabel())
                                || !genTableColumn1.getNotnull().equals(genTableColumnList.get(i).getNotnull())
                                || !genTableColumn1.getDefaultValue().equals(genTableColumnList.get(i).getDefaultValue())
                                || !genTableColumn1.getExtensionList().equals(genTableColumnList.get(i).getExtensionList())
                                || !genTableColumn1.getColType().equals(genTableColumnList.get(i).getColType())) {
                            if (genTableColumnList.get(i).getExtensionList().contains("yyyy")) {
                                if (genTableColumnList.get(i).getExtensionList().contains("HH:mm")) {
                                    if (genTableColumnList.get(i).getDefaultValue().contains("CURRENT_TIMESTAMP")) {
                                        String sql9 = "alter table " + genTableColumnList.get(i).getTableName() + "  MODIFY COLUMN " + genTableColumnList.get(i).getColName() + " timestamp  ";
                                        if (String.valueOf(genTableColumnList.get(i).getNotnull()).contains("1")) {
                                            sql9 = sql9 + " NOT NULL ";
                                        }
                                        sql9 = sql9 + " DEFAULT  CURRENT_TIMESTAMP  COMMENT '" + genTableColumnList.get(i).getFieldLabel() + "'";
                                        DBUtil.executeUpdateFH(sql9);
                                    } else {
                                        String sql9 = "alter table " + genTableColumnList.get(i).getTableName() + "  MODIFY COLUMN " + genTableColumnList.get(i).getColName() + " timestamp   ";
                                        if (String.valueOf(genTableColumnList.get(i).getNotnull()).contains("1")) {
                                            sql9 = sql9 + " NOT NULL ";
                                        }
                                        sql9 = sql9 + " COMMENT '" + genTableColumnList.get(i).getFieldLabel() + "'";
                                        DBUtil.executeUpdateFH(sql9);
                                    }
                                } else {
                                    String sql9 = "alter table " + genTableColumnList.get(i).getTableName() + "  MODIFY COLUMN " + genTableColumnList.get(i).getColName() + " date  NOT NULL    COMMENT '" + genTableColumnList.get(i).getFieldLabel() + "'";
                                    DBUtil.executeUpdateFH(sql9);
                                }
                            } else {
                                String leixing = "";
                                if (genTableColumnList.get(i).getFieldPackagingType().equals("String")) {
                                    leixing = "varchar";
                                } else if (genTableColumnList.get(i).getFieldPackagingType().equals("Long")) {
                                    leixing = "bigint";
                                } else if (genTableColumnList.get(i).getFieldPackagingType().equals("Integer")) {
                                    leixing = "smallint";
                                } else if (genTableColumnList.get(i).getFieldPackagingType().equals("Boolean")) {
                                    leixing = "tinyint";
                                } else if (genTableColumnList.get(i).getFieldPackagingType().equals("Double")) {
                                    if (genTableColumnList.get(i).getExtensionList().contains("fixed")) {
                                        leixing = "decimal";
                                    } else if (genTableColumnList.get(i).getExtensionList().contains("float")) {
                                        leixing = "double";
                                    }
                                }
                                String sql9 = "";
                                if (leixing.contains("decimal")||leixing.contains("double")){
                                    sql9 = "alter table " + genTableColumnList.get(i).getTableName() + "  MODIFY COLUMN `" + genTableColumnList.get(i).getColName() + "` " + leixing + "(" + genTableColumnList.get(i).getColLength() + "," + genTableColumnList.get(i).getDecimal() + ")";
                                }
                                else {
                                    sql9 = "alter table " + genTableColumnList.get(i).getTableName() + "  MODIFY COLUMN `" + genTableColumnList.get(i).getColName() + "` " + leixing + "(" + genTableColumnList.get(i).getColLength() +  ")";
                                }
                                if (genTableColumnList.get(i).getExtensionList().contains("unsigned")) {
                                    sql9 = sql9 + " unsigned ";
                                }
                                if (String.valueOf(genTableColumnList.get(i).getNotnull()).contains("1")) {
                                    sql9 = sql9 + " NOT NULL ";
                                }
                                if (genTableColumnList.get(i).getDefaultValue() != null) {
                                    if (genTableColumnList.get(i).getDefaultValue() != "") {
                                        sql9 = sql9 + " DEFAULT " + genTableColumnList.get(i).getDefaultValue();
                                    }
                                }
                                sql9 = sql9 + " COMMENT '" + genTableColumnList.get(i).getFieldLabel() + "' ";
                                DBUtil.executeUpdateFH(sql9);
                            }
                        }
                    }
                }
            }
        }

        dibotColumDao.delete(new QueryWrapper<GenColumn>().eq("table_name", genTableColumnList.get(0).getTableName()));
        //生成diboot_colume记录
        for (GenTableColumn genTableColumn : genTableColumnList) {
            GenColumn genColumn = new GenColumn();
            genColumn.setTableName(genTableColumn.getTableName());
            genColumn.setColName(genTableColumn.getColName());
            genColumn.setColIndex(genTableColumn.getColIndex());
            genColumn.setColType(genTableColumn.getColType());
            genColumn.setDecimals(genTableColumn.getDecimal());//小数位
            genColumn.setColLength(genTableColumn.getColLength());//位数
            genColumn.setNotnull(genTableColumn.getNotnull());
            genColumn.setDefaultValue(genTableColumn.getDefaultValue());//默认值
            genColumn.setFieldName(genTableColumn.getFieldName());
            genColumn.setFieldLabel(genTableColumn.getFieldLabel());
            genColumn.setExtensions(genTableColumn.getExtensionList());//日期格式或整型数设置
            genColumn.setRefType(genTableColumn.getRefType());//关联类型
            genColumn.setRefTable(genTableColumn.getRefTable());//关联表
            genColumn.setRefColumn(genTableColumn.getRefColumn());//关联字段
            genColumn.setAsColumn(genTableColumn.getAsColumn());//显示字段
            genColumn.setFormType(genTableColumn.getFormType());//表单类型
            genColumn.setCreateTime(new Date());
            genColumn.setUpdateTime(new Date());
            genColumn.setIsInsert(Integer.valueOf(String.valueOf(genTableColumn.getIsInsert())));
            genColumn.setIsList(Integer.valueOf(String.valueOf(genTableColumn.getIsList())));
            genColumn.setIsQuery(Integer.valueOf(String.valueOf(genTableColumn.getIsQuery())));
            genColumn.setQueryType(String.valueOf(genTableColumn.getQueryType()));
            if (genTableColumn.getRefTable()!=null){
                genColumn.setRefTable(genTableColumn.getRefTable());
            }
            if (genTableColumn.getRefColumn()!=null){
                genColumn.setRefColumn(genTableColumn.getRefColumn());
            }
            if (genTableColumn.getRefType()!=null){
                genColumn.setRefType(genTableColumn.getRefType());
            }
            dibotColumDao.insert(genColumn);
        }


        return R.ok("更新成功！");
    }

    @Override
    public void createdibootcolumn() throws SQLException, ClassNotFoundException {
        String sql2="CREATE TABLE `gen_column`  (\n" +
                "  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',\n" +
                "  `table_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表名',\n" +
                "  `col_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '列名',\n" +
                "  `col_index` smallint(4) NULL DEFAULT NULL COMMENT '列顺序',\n" +
                "  `col_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类型',\n" +
                "  `col_length` smallint(5) NULL DEFAULT 0 COMMENT '字段长度',\n" +
                "  `decimals` tinyint(4) NULL DEFAULT 0 COMMENT '小数位',\n" +
                "  `notnull` tinyint(1) NULL DEFAULT 0 COMMENT '非空',\n" +
                "  `default_value` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '默认值',\n" +
                "  `key_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主键唯一',\n" +
                "  `field_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性名',\n" +
                "  `field_label` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性标签',\n" +
                "  `extensions` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扩展',\n" +
                "  `ref_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联类型',\n" +
                "  `ref_table` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联表',\n" +
                "  `ref_column` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联字段',\n" +
                "  `as_column` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '显示字段',\n" +
                "  `form_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'INPUT' COMMENT '表单类型',\n" +
                "  `extdata` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扩展参数',\n" +
                "  `create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',\n" +
                "  `update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',\n" +
                "  `is_insert` char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否插入字段',\n" +
                "  `is_list` char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否列表字段',\n" +
                "  `is_query` char(1) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否查询字段',\n" +
                "  `query_type` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ' 查询方式（等于、不等于、大于、小于、范围、左LIKE、右LIKE、左右LIKE）',\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `idx_gen_column`(`table_name`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1153557909270712322 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '列定义' ROW_FORMAT = Dynamic;";
        DBUtil.executeUpdateFH(sql2);
    }
}
